#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
Python 3.x
SQLAlchemy 1.4.41

SQLAlchemy 表关系
一对一
"""
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from constants import DB_URI
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(DB_URI, echo=True)
# 所有的类都要继承自`declarative_base`这个函数生成的基类
Base = declarative_base(engine)
Session = sessionmaker(bind=engine)
session = Session()

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(50), nullable=False)
    # Author表的外键，指定外键的时候，是使用的是数据库表的名称，而不是类名
    author_id = Column(Integer, ForeignKey('author.id'))
    # 在ORM层面绑定两者之间的关系，第一个参数是绑定的表的类名，
    # 第二个参数back_populates是通过Author反向访问时的字段名称
    authors = relationship('Author', back_populates="addresses")

    def __repr__(self):
        return "<Address(id='%s', email_address='%s', author_id='%s')>" % (self.id, self.email_address, self.author_id)


class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    # 设置uselist关键字参数为False
    addresses = relationship("Address", order_by=Address.id, back_populates="authors", uselist=False)

    def __repr__(self):
        return "<Author(id='%s', username='%s')>" % (self.id, self.username)


# 映射到数据库
Base.metadata.drop_all()
Base.metadata.create_all()

author = Author(username="YiYeBiJi")
# author.addresses = [
#     Address(email_address='YiYeBiJi@126.com'),
#     Address(email_address='YiYeBiJi@163.com')
# ]
# 如指定addresses为数组，出现以下错误
# >  Traceback (most recent call last):
# > ...
# > AttributeError: 'list' object has no attribute '_sa_instance_state'
author.addresses = Address(email_address='YiYeBiJi@126.com')
session.add(author)
session.commit()

tmp_address = session.query(Address).all()
print(tmp_address)
# > [<Address(id='1', email_address='YiYeBiJi@126.com', author_id='1')>]
for tmp in tmp_address:
    print(tmp.authors)
# > <Author(id='1', username='YiYeBiJi')>
tmp_author = session.query(Author).all()
print(tmp_author)
# > [<Author(id='1', username='YiYeBiJi')>]
